﻿Imports System
Imports System.Data
Imports System.Xml
Imports System.Data.SqlClient
Imports System.Collections
Imports System.IO
Namespace ThietBi.LQ
    Public Class BaseDB
        Public Shared sqlConnect As SqlConnection

        'Public Shared Function GetConnectStrFromFile(ByVal pFileSpec As String) As String
        '    Dim mPassFromFile As String = ""
        '    Dim mCnnStr As String = ""
        '    Try
        '        Dim sr As StreamReader = New StreamReader(pFileSpec)
        '        Dim line As String
        '        Do
        '            line = sr.ReadLine()
        '            If Not IsNothing(line) Then
        '                Dim str As String = "Provider=SQLOLEDB.1;"
        '                Dim index = line.IndexOf(str) + str.Length()
        '                If index = str.Length() Then
        '                    mCnnStr = line.Substring(index)
        '                    Exit Do
        '                End If
        '            End If
        '        Loop Until line Is Nothing
        '        sr.Close()
        '    Catch ex As Exception
        '        Throw ex
        '    End Try
        '    'mCnnStr &= ";Pooling = true; Min Pool Size = 1; Max Pool Size = 50"
        '    'myConnStr = mCnnStr
        '    Return mCnnStr
        'End Function

        'Public Shared Sub GanCauLenhKetNoi(ByRef strConnect As String, ByVal sUserName As String, ByVal sPassword As String)
        '    ThayTheGiaTri(strConnect, "User ID", sUserName)
        '    ThayTheGiaTri(strConnect, "Password", sPassword)
        'End Sub
        ''ham dung de thay gia tri cua mot thanh phan trong chuoi
        'Public Shared Sub ThayTheGiaTri(ByRef strConnect As String, ByVal sThanhPhan As String, ByVal sGiaTri As String)
        '    Dim sThayThe As String = sThanhPhan & "=" & sGiaTri
        '    Dim viTriDau As Integer = strConnect.IndexOf(sThanhPhan)
        '    'neu tim thay
        '    If (viTriDau <> -1) Then
        '        'delete het cho den khi gap ";" hoac cuoi chuoi
        '        'tim vi tri cuoi cua chuoi nay

        '        Dim i As Integer = 0
        '        While (strConnect(viTriDau + i) <> ";" And viTriDau + i < strConnect.Length)
        '            i += 1
        '        End While
        '        'delete
        '        strConnect = strConnect.Remove(viTriDau, i)
        '        'thay the bang chuoi moi
        '        strConnect = strConnect.Insert(viTriDau, sThayThe)
        '    Else
        '        sThayThe = ";" & sThayThe
        '        strConnect = String.Concat(strConnect, sThayThe)
        '    End If
        'End Sub
        Public Shared connectStr As String = "Data Source=10.0.0.99\SQLEXPRESS;Initial Catalog=ThietBi;User ID=sa;pwd=sql2005"

        Public Shared Sub OpenConnect(Optional ByVal dbName As String = "")
            Try
                'Mo ket noi CSDL
                sqlConnect = New SqlConnection("Data Source=10.0.0.99\SQLEXPRESS;Initial Catalog=ThietBi;User ID=sa;pwd=sql2005")
                'Dim connectString = ""
                'If connectString = "" Then
                '    connectString = 'openConnStr ' GetConnectStrFromFile("DBConnect.udl")
                '    'connectString = GetConnectStrFromFile("DBConnect.udl")
                'End If
                'sqlConnect.ConnectionString = connectString

                ''Neu ket noi voi dbName khac
                'If dbName <> "" Then
                '    Dim sConnectString As String = sqlConnect.ConnectionString()
                '    Dim indexStart, indexEnd As Integer
                '    indexStart = sConnectString.IndexOf("Initial Catalog=") + 16
                '    indexEnd = sConnectString.IndexOf(";Data Source")
                '    Dim strReplace = sConnectString.Substring(indexStart, indexEnd - indexStart)
                '    sqlConnect.ConnectionString = sConnectString.Replace(strReplace, dbName)
                'End If

                'GanCauLenhKetNoi(sqlConnect.ConnectionString, g_UserName, g_Password)
                If sqlConnect.State = ConnectionState.Closed Then
                    sqlConnect.Open()
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        Public Shared Sub CloseConnect()
            Try
                If sqlConnect.State = ConnectionState.Open Then
                    sqlConnect.Close()
                End If
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

        'Khoi tao SqlCommand 1
        Public Shared Function InitializeConnect_Command(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As SqlCommand
            'Thuc thi store procedure
            Dim com As New SqlCommand(strProcedureName, sqlConnect)
            OpenConnect()
            com.Connection = sqlConnect
            com.CommandType = CommandType.StoredProcedure
            For i As Integer = 0 To UBound(par)
                com.Parameters.Add(par(i))
            Next
            Return com
        End Function

        'Khoi tao SqlCommand 2
        Public Shared Function InitializeConnect_Command(ByVal strProcedureName As String) As SqlCommand
            'Thuc thi store procedure
            Dim com As New SqlCommand(strProcedureName)
            OpenConnect()
            com.Connection = sqlConnect
            com.CommandType = CommandType.StoredProcedure
            Return com
        End Function

        'Ham thuc thi nhung procedure kiem tra
        Public Shared Function ExecSqlProcedure_Command(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As SqlCommand
            Dim Result As Boolean = False
            Dim com As SqlCommand
            com = InitializeConnect_Command(strProcedureName, par)
            Dim lRowsAffected As Long = 0
            Try
                com.ExecuteReader()
            Catch ex As Exception
                Throw ex
            End Try
            Return com
        End Function
        'Ham thuc thi pro nhung bo qua expride timeout, ko bat ex
        Public Shared Function ExecSqlProcedure_Command(ByVal strProcedureName As String) As SqlCommand
            Dim Result As Boolean = False
            Dim com As SqlCommand
            com = InitializeConnect_Command(strProcedureName)
            Dim lRowsAffected As Long = 0
            Try
                com.ExecuteReader()
            Catch ex As Exception
                'Throw ex
            End Try
            Return com
        End Function

        'Ham thuc thi nhung procedure insert, update, delete 1
        Public Shared Function ExecSqlProcedure_NoneQuery(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As Long
            Dim Result As Boolean = False
            Dim com As SqlCommand
            com = InitializeConnect_Command(strProcedureName, par)
            Dim lRowsAffected As Long = 0
            Try
                com.Transaction = com.Connection.BeginTransaction()
                lRowsAffected = com.ExecuteNonQuery()
                com.Transaction.Commit()
            Catch ex As Exception
                com.Transaction.Rollback()
                Throw ex
            Finally
                com.Connection.Close()
            End Try
            Return lRowsAffected
        End Function

        Public Shared Function ExecSql_NoneQuery(ByVal strSql As String) As Long
            Dim com As SqlCommand
            com = New SqlCommand
            Dim lRowsAffected As Long = 0
            Try
                com.CommandText = strSql
                OpenConnect()
                com.Connection = sqlConnect
                com.Transaction = com.Connection.BeginTransaction()
                lRowsAffected = com.ExecuteNonQuery()
                com.Transaction.Commit()
                com.Connection.Close()
            Catch ex As Exception
                com.Transaction.Rollback()
                Throw ex
            End Try
            Return lRowsAffected
        End Function
        Public Shared Function ExecSql_DataTable(ByVal strSql As String) As DataTable
            Dim com As New SqlCommand
            Dim tb As New DataTable
            Try
                com.CommandText = strSql
                OpenConnect()
                com.Connection = sqlConnect
                Dim da As New SqlDataAdapter
                da.SelectCommand = com
                da.Fill(tb)
            Catch ex As Exception
                Throw ex
            Finally
                com.Connection.Close()
            End Try
            Return tb

        End Function
        Public Shared Function ExecSql_SqlDataAdapter(ByVal strSql As String) As SqlDataAdapter
            Dim com As New SqlCommand
            Dim da As New SqlDataAdapter
            Try
                com.CommandText = strSql
                OpenConnect()
                com.Connection = sqlConnect

                da.SelectCommand = com
                'da.Fill(tb)
            Catch ex As Exception
                Throw ex
            Finally
                com.Connection.Close()
            End Try
            Return da

        End Function
        'Thuc thi nhung query procedure 1
        Public Shared Function ExecSqlProcedure_DataSet(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As DataSet
            Dim Result As Boolean = False
            Dim com As SqlCommand
            com = InitializeConnect_Command(strProcedureName, par)
            Dim myAdapter As New SqlDataAdapter(com)
            Dim ds As New DataSet
            myAdapter.Fill(ds, strProcedureName)
            com.Parameters.Clear()
            com.Connection.Close()
            Return ds
        End Function

        'Thuc thi nhung query procedure 2
        Public Shared Function ExecSqlProcedure_DataSet(ByVal strProcedureName As String) As DataSet
            Dim Result As Boolean = False
            Dim com As SqlCommand
            com = InitializeConnect_Command(strProcedureName)
            Dim myAdapter As New SqlDataAdapter(com)
            Dim ds As New DataSet
            Try
                myAdapter.Fill(ds, strProcedureName)
            Catch ex As Exception
                Throw ex
            Finally
                com.Connection.Close()
            End Try
            Return ds
        End Function

        Public Shared Function ExecSqlProcedure_DataReader(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As SqlDataReader
            Dim Result As Boolean = False
            Dim com As SqlCommand
            com = InitializeConnect_Command(strProcedureName, par)
            Dim myReader As SqlDataReader
            Try
                myReader = com.ExecuteReader()
            Catch ex As Exception
                Throw ex
            End Try
            Return myReader
        End Function

        Public Shared Function ExecSql_DataReader(ByVal strSql As String) As SqlDataReader
            Dim com As New SqlCommand
            com.CommandText = strSql
            OpenConnect()
            com.Connection = sqlConnect
            Dim myReader As SqlDataReader
            Try
                myReader = com.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                Throw ex
            End Try
            Return myReader
        End Function


        Public Shared Function ExecSql_DataSet(ByVal strSql As String) As DataSet
            Dim com As New SqlCommand
            Dim ds As New DataSet
            Try
                com.CommandText = strSql
                OpenConnect()
                com.Connection = sqlConnect
                Dim da As New SqlDataAdapter
                da.SelectCommand = com
                da.Fill(ds)
            Catch ex As Exception
                Throw ex
            Finally
                com.Connection.Close()
            End Try
            Return ds
        End Function

        Public Shared Function ExecSql_DataValue(ByVal strSql As String) As Object
            Dim com As New SqlCommand
            com.CommandText = strSql
            OpenConnect()
            com.Connection = sqlConnect
            Dim result As Object
            Try
                result = com.ExecuteScalar()
            Catch ex As Exception
                Throw ex
            Finally
                com.Connection.Close()
            End Try
            Return result
        End Function

        Public Shared Function ExecSqlProcedure_DataValue(ByVal strProcedureName As String, ByVal ParamArray par() As SqlParameter) As Object
            Dim com As SqlCommand
            com = InitializeConnect_Command(strProcedureName, par)
            Dim result As Object
            Try
                result = com.ExecuteScalar()
            Catch ex As Exception
                Throw ex
            Finally
                com.Connection.Close()
            End Try
            Return result
        End Function

        Public Shared Function GetObjectValue(ByVal value As Object) As Object
            If value Is DBNull.Value Then
                Return Nothing
            Else
                Return value
            End If
        End Function
    End Class
End Namespace
